Re: Foreign Key Problem

At 10:53 PM -0500 12/26/03, Tom Lane wrote:

>generated as a SERIAL sequence (which essentially means you abdicate
>responsibility for choosing its values) how could it be a valid
>reference to pre-existing entries in another table?
>
>What are you trying to accomplish, exactly?


First of all, there's the schema of the whole database up at 
. 
The database creation script is elsewhere in this blog; it was 
created originally for MS SQL Server and I adapted the script for use 
with PostgreSQL.  The blogger seems to have abandoned this particular 
project, so I don't think more information about the database will be 
forthcoming any time soon.  I do think this schema seems a bit 
complex, but she seems to have good reasons for doing it like she 
did.  I changed _key to _id, though, since it makes more sense to me 
and this is what I'm used to seeing at work.

I'm adapting this database for personal use, to keep track of my 
books, needles, yarn, etc.  Eventually I'll be expanding this 
database to keep track of my spinning projects, after I've figured my 
way around this schema.  I'm using ColdFusion as the front end with 
the database.

Needlestyle and needles do need to be separate.  There are only 4 
styles of needles - circular, flex jumpers, single pointed and 
doublepointed.  But, there are different sizes and lengths for each 
type of the needle.  For instance, one manufacturer will have 16 
sizes that have a length of 24 inchess (see 
http://www.patternworks.com/PWShopping/needles.asp for example).


>  > INSERT INTO "needles" ("needles_id", "needle_style_id", "needle_mm",
>>  "needle_length", "needle_lud") VALUES
>>  (nextval('public.needles_needles_id_seq'::text), '1', '2.25', '24"',
>>  NULL)
>>  ERROR:  insert or update on table "needles" violates foreign key 
>>constraint "$1"
>
>>  What could be the problem?
>
>See above.  You generated a value for needles_id that doesn't match any
>row in the accessory table.


Hmm, that could make sense.  What I've been doing is filling in some 
of the tables to see how it all works and to provide test data to use 
with the ColdFusion front end that I'm developing.

-- 

Lola - mailto:lola@his.com
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
Check out this blog:  http://www.denbeste.nu
I'm in Bowie, MD, USA, halfway between DC and Annapolis.